package com.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.h2.tools.DeleteDbFiles;
import org.hibernate.Session;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;

/**
 * A very simple class that shows how to load the driver, create a database,
 * create a table, and insert some data.
 */
public class HelloWorld {

	/**
	 * Called when ran from command line.
	 * 
	 * @param args
	 *            ignored
	 */
	public static void main(String... args) throws Exception {
		// delete the database named 'test' in the user home directory
		DeleteDbFiles.execute("~", "test", true);

		test();
		testEM();
	}

	private static Connection getConnectionFixed()
			throws ClassNotFoundException, SQLException {
		Class.forName("org.h2.Driver");
		Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa",
				"");
		return conn;
	}

	private static Connection getConnectionFromEntityManager(EntityManager em)
			throws SQLException {
		Session session = em.unwrap(Session.class);
		SessionFactoryImplementor sfi = (SessionFactoryImplementor) session
				.getSessionFactory();
		ConnectionProvider cp = sfi.getConnectionProvider();
		Connection conn = cp.getConnection();
		return conn;
	}

	private static void test() throws Exception {
		Connection conn = getConnectionFixed();
		Statement stat = conn.createStatement();
		stat.execute("create table test(id int primary key, name varchar(255))");
		stat.execute("insert into test values(1, 'Hello')");
		stat.execute("insert into test values(2, 'Good Bye')");
		ResultSet rs;
		rs = stat.executeQuery("select * from test");
		while (rs.next()) {
			System.out.println(rs.getString("name"));
		}
		stat.close();
		conn.close();
	}

	private static void testEM() throws Exception {
		HelloEM emg = new HelloEM();

		// Connection conn = getConnectionFromEntityManager(emg.em);

		emg.test();

		// Query query = session.getNamedQuery("findStockByStockCodeNativeSQL")

		TypedQuery<Formation> queryProducts = emg.em.createNamedQuery(
				"Formation.getAll", Formation.class);
		List<Formation> products = queryProducts.getResultList();
		for (Formation f : products) {
			System.out.println(f.getTheme());
		}

		System.out.println("#-----#");

		TypedQuery<Formation> queryProductsByName = emg.em.createNamedQuery(
				"Formation.getAllByName", Formation.class);
		queryProductsByName.setParameter("theme", "Hibernate"); // Case
																// sensitive
		products = queryProductsByName.getResultList();
		for (Formation f : products) {
			System.out.println(f.getTheme());
		}
		emg.close();
	}

}
